# -*- coding: utf-8 -*-
"""
Created on Fri Jan  8 15:14:18 2021

@author: PC
"""

from sqlalchemy import create_engine
conn=create_engine('mysql+pymysql://root:123456@localhost:3306/kkf?charset=utf8')
import pandas as pd
df=pd.read_sql("select * from tbl_course", conn)
df1=pd.DataFrame(df)
df2=pd.read_csv(r'C:\Users\PC\Desktop\考试0108\lecturer.csv')
df2.rename(columns={'id':'lecturer_id'},inplace=True)
df3=pd.merge(df1,df2,how='inner',on='lecturer_id')
df3=df3[df3["工龄"]<=9]
df3=df3[df3["工龄"]>3]
df3['class_hours']=df3['class_hours'].map(lambda x:x.rstrip(','))
df3['class_hours']=df3['class_hours'].map(lambda x:x.rstrip(' '))
df3['class_hours']=df3['class_hours'].astype(int)
df3['hours_per_weeks']=df3['hours_per_week'].astype(int)
df3['class_weeks']=df3['class_hours']/df3['hours_per_week'] 
df3=df3.sort_values('id',ascending=True)
df3[['id','name','lecturer','class_weeks']].to_sql("test1",conn,index=False,index_label='id')